In the recent Query Tuning project, one of the developers who were helping me out in the project asked me if there is any way that he could know how many pages are used by any Index, and if there is any way I could demonstrate the different levels of B-Tree.
The following is the diagram on Clustered Index that I have quickly drawn using MS Word for the said developer.
- Clustered Index B-Tree
Let us quickly see the diagram of B-Tree and how the levels are set up. The leaf level is always considered as Level 0. There can be many levels of the intermediate nodes. In the example above, I have listed only one intermediate node for demonstration purposes.
We can use Dynamic Management Views to figure out how many different levels are there for any Index, as well as how many rows are stored at each level and the number of pages used for all Index.
Let us run the following commands and generate a table with data. We will create a Clustered Index so we can have B-Tree structure.
USE tempdb
GO
-- Create Table FragTable
CREATE TABLE FragTable (ID CHAR(800),
FirstName CHAR(2000),
LastName CHAR(3000),
City CHAR(2253))
GO
-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_FragTable_ID] ON FragTable
(
[ID] ASC
) ON [PRIMARY]
GO
-- Insert one Million Records
INSERT INTO FragTable (ID,FirstName,LastName,City)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the percentages
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Clean up
DROP TABLE FragTable
GO
Now let us check the result-set of the table.
From the resultset above, we can see that there are multiple levels of the Index. In our example, we have 4 levels of Index, and each level has different numbers of pages and rows. In one of the future articles I will post, we will analyze the result in a deeper sense.
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
Interesting.
Do you know how to delete pages in sql related to a dnn site? Where do i look to in sql to find these pages? I have about 150 pages i need to delete and manually is very time consuming. I honestly can’t believe dnn does not have the feature of deleting multiple pages at the same time. I do not have a backup to restore to. If you know how, please PM my email. Thank you very much for your help and all your helpful posts.
Hello Pinal,
I’m a little dazzled.
In theory, can i have multiples pages in a leaf level?
best regads,
Valmir
How page_count effects the performance?
i want to make mlm database.
that table is this.
create table mlm(
MemberID int,
RootID int ,
Position varchar(2),
Name varchar(10),
)
insert into mlm values(1,0,’Left’,’A’)
insert into mlm values(2,1,’Centre’,’B’)
insert into mlm values(3,1,’Right’,’C’)
insert into mlm values(4,1,’Left’,’D)
insert into mlm values(5,2,’Centre’,’E’)
insert into mlm values(6,2,’Right’,’F’)
/*i wan to count total left id of 1 Member ID that is 4 */
2 is 1,
.
but how to count in sql server this system
select Memberid,count(*) from mim
where Position=’Left’
group by MemeberId
hi sir
i want to manage the level of user for MLM
plz give me solution of how to manage level
ex-
Memberid SponsorId
100 NULL
101 100
102 100
103 100
104 101
105 101
106 101
107 102
108 102
109 102
110 103
111 103
112 103
113 104
…
…
…
from 104 to 113 has 0 level and 101,102 and 103 has been completed his 1st level and 100 has been completed his 2 level
how to i manage the level of user dynamically after user registration
plz help me sir
database design —–
Memberid ———-SponsorId
100 —————– NULL
101 —————– 100
102 —————– 100
103 —————– 100
104 —————– 101
105 —————– 101
106 —————– 101
107 —————– 102
108 —————– 102
109 —————– 102
110 —————– 103
111 —————– 103
112 —————– 103
113 —————– 104